package au.edu.utas.yu_qiao_group.luckyman.db.raffles;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import java.util.ArrayList;

public class RaffleTable {
  public static final String TABLE_NAME = "raffles";
  public static final String TRIGGER_NAME_1 = "raffles_update_timestamp_trigger";

  public static final String KEY_RAFFLE_ID = "id";
  public static final String KEY_NAME = "name";
  public static final String KEY_DESC = "desc";
  public static final String KEY_ORGANIZER = "organizer";
  public static final String KEY_COVER = "cover";
  public static final String KEY_TICKET_TOTAL = "ticket_total";
  public static final String KEY_TICKET_REMAIN = "ticket_remain";
  public static final String KEY_SINGLE_LIMIT = "single_limit";
  public static final String KEY_AWARD_PRICE = "award_price";
  public static final String KEY_UNIT_PRICE = "unit_price";
  public static final String KEY_DRAW_TIME = "draw_time";
  public static final String KEY_DRAW_METHOD = "draw_method"; // 0: normal, 1: margin
  public static final String KEY_CREATE_TIME = "create_time";
  public static final String KEY_UPDATE_TIME = "update_time";
  public static final String KEY_DEL_FLAG = "del_flg";

  public static final String CREATE_STATEMENT = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME
          + " (" + KEY_RAFFLE_ID + " integer primary key autoincrement, "
          + KEY_NAME + " string not null, "
          + KEY_DESC + " string not null, "
          + KEY_ORGANIZER + " string not null, "
          + KEY_COVER + " varchar, "
          + KEY_UNIT_PRICE + " integer not null, "
          + KEY_TICKET_TOTAL + " integer not null, "
          + KEY_TICKET_REMAIN + " integer not null, "
          + KEY_SINGLE_LIMIT + " integer not null, "
          + KEY_AWARD_PRICE + " integer not null, "
          + KEY_DRAW_TIME + " datetime not null, "
          + KEY_DRAW_METHOD + " tinyint not null, "
          + KEY_CREATE_TIME + " datetime default current_timestamp, "
          + KEY_UPDATE_TIME + " datetime default current_timestamp, "
          + KEY_DEL_FLAG + " tinyint default 0"
          + ");";

  public static final String CREATE_TRIGGER_STATEMENT = "CREATE TRIGGER " + TRIGGER_NAME_1
          + " AFTER UPDATE ON " + TABLE_NAME
          + " BEGIN"
          + " UPDATE " + TABLE_NAME + " SET " + KEY_UPDATE_TIME + " = current_timestamp WHERE "
          + KEY_RAFFLE_ID + " = NEW." + KEY_RAFFLE_ID
          + "; END;";

  public static RaffleDTO createFromCursor(Cursor c) {
    if (c == null || c.isAfterLast() || c.isBeforeFirst()) {
      return null;
    } else {
      RaffleDTO r = new RaffleDTO(
              c.getInt(c.getColumnIndex(KEY_RAFFLE_ID)),
              c.getString(c.getColumnIndex(KEY_NAME)),
              c.getString(c.getColumnIndex(KEY_DESC)),
              c.getString(c.getColumnIndex(KEY_ORGANIZER)),
              c.getString(c.getColumnIndex(KEY_COVER)),
              c.getInt(c.getColumnIndex(KEY_UNIT_PRICE)),
              c.getInt(c.getColumnIndex(KEY_TICKET_TOTAL)),
              c.getInt(c.getColumnIndex(KEY_TICKET_REMAIN)),
              c.getInt(c.getColumnIndex(KEY_SINGLE_LIMIT)),
              c.getInt(c.getColumnIndex(KEY_AWARD_PRICE)),
              c.getLong(c.getColumnIndex(KEY_DRAW_TIME)),
              c.getInt(c.getColumnIndex(KEY_DRAW_METHOD))
      );
      return r;
    }
  }

  public static long insert(SQLiteDatabase db, RaffleDTO p) {
    ContentValues values = new ContentValues();
    values.put(KEY_NAME, p.getMName());
    values.put(KEY_DESC, p.getMDesc());
    values.put(KEY_ORGANIZER, p.getMOrganizer());
    values.put(KEY_COVER, p.getMCover());
    values.put(KEY_TICKET_TOTAL, p.getMTicketTotal());
    values.put(KEY_TICKET_REMAIN, p.getMTicketRemain());
    values.put(KEY_SINGLE_LIMIT, p.getMSingleLimit());
    values.put(KEY_AWARD_PRICE, p.getMAwardPrice());
    values.put(KEY_UNIT_PRICE, p.getMUnitPrice());
    values.put(KEY_DRAW_TIME, p.getMDrawTime());
    values.put(KEY_DRAW_METHOD, p.getMDrawMethod());
    return db.insertOrThrow(TABLE_NAME, null, values);
  }

  public static ArrayList<RaffleDTO> selectAll(SQLiteDatabase db) {
    ArrayList<RaffleDTO> results = new ArrayList<RaffleDTO>();
    Cursor c = db.query(TABLE_NAME, null, KEY_DEL_FLAG + " = ?", new String[]{"0"}, null, null, KEY_CREATE_TIME);
    if (c != null) {
      c.moveToFirst();
      while (!c.isAfterLast()) {
        results.add(createFromCursor(c));
        c.moveToNext();
      }
    }
    return results;
  }

  public static RaffleDTO getByID(SQLiteDatabase db, int id) {
    Cursor c = db.query(TABLE_NAME, null, KEY_RAFFLE_ID + " = ? and " + KEY_DEL_FLAG + " = ?", new String[]{"" + id, "0"}, null, null, KEY_CREATE_TIME);
    c.moveToFirst();
    return createFromCursor(c);
  }

  public static void update(SQLiteDatabase db, RaffleDTO r) {
    ContentValues values = new ContentValues();
    values.put(KEY_NAME, r.getMName());
    values.put(KEY_DESC, r.getMDesc());
    values.put(KEY_UNIT_PRICE, r.getMUnitPrice());
    values.put(KEY_ORGANIZER, r.getMOrganizer());
    values.put(KEY_COVER, r.getMCover());
    values.put(KEY_SINGLE_LIMIT, r.getMSingleLimit());
    values.put(KEY_AWARD_PRICE, r.getMAwardPrice());
    db.update(TABLE_NAME, values, KEY_RAFFLE_ID + "= ? and " + KEY_DEL_FLAG + " = ?", new String[]{"" + r.getMRaffleID(), "0"});
  }

  public static void updateTicketRemain(SQLiteDatabase db, RaffleDTO r, int remain) {
    ContentValues values = new ContentValues();
    values.put(KEY_TICKET_REMAIN, remain);
    db.update(TABLE_NAME, values, KEY_RAFFLE_ID + "= ? and " + KEY_DEL_FLAG + " = ?", new String[]{"" + r.getMRaffleID(), "0"});
  }

  public static void delete(SQLiteDatabase db, RaffleDTO r) {
    ContentValues values = new ContentValues();
    values.put(KEY_DEL_FLAG, 1);
    db.update(TABLE_NAME, values, KEY_RAFFLE_ID + "= ?", new String[]{"" + r.getMRaffleID()});
  }
}
